package com.mao.service.impl.searchvoucher;

import java.io.OutputStream;
import java.util.List;

import jxl.Workbook;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.NumberFormat;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.mao.action.jsonvo.searchcenter.JsonVoSearchProfit;
import com.mao.action.jsonvo.searchcenter.JsonVoSearchPurchaseByProduct;
import com.mao.action.jsonvo.searchcenter.JsonVoSearchVoucherByProduct;
import com.mao.action.jsonvo.searchcenter.JsonVoVoucherProduct;
import com.mao.dao.organization.OrganizationClassDao;
import com.mao.dao.product.ProductClassDao;
import com.mao.dao.searchvoucher.SearchVoucherDao;
import com.mao.dao.warehouse.WareHouseClassDao;
import com.mao.model.organization.OrganizationClass;
import com.mao.model.product.ProductClass;
import com.mao.model.voucher.ProductVoucherBase;
import com.mao.model.voucher.VoucherBase;
import com.mao.model.voucher.VoucherType;
import com.mao.model.warehouse.WareHouseClass;
import com.mao.service.searchvoucher.ISearchVoucherService;
import com.mao.util.Validator;

@Service("searchVoucherService")
public class SearchVoucherServiceImpl implements ISearchVoucherService {

	private SearchVoucherDao searchVoucherDao;

	private ProductClassDao productClassDao;
	private OrganizationClassDao orgClassDao;
	private WareHouseClassDao wareHouseClassDao;

	public List<VoucherBase> searchVoucher(Long storeid, Integer page,
			Integer rows, SearchVoucherCondition searchVoucherCondition) {
		return searchVoucherDao.searchVoucher(storeid, page, rows,
				searchVoucherCondition);
	}

	public int searchVoucherCount(Long storeid, Integer page, Integer rows,
			SearchVoucherCondition searchVoucherCondition) {
		return searchVoucherDao.searchVoucherCount(storeid, page, rows,
				searchVoucherCondition);
	}

	public VoucherBase getVoucherById(String voucherType, Long id) {
		return searchVoucherDao.getVoucherById(voucherType, id);
	}

	public List<JsonVoVoucherProduct> searchProductsByVoucher(long voucherid,
			VoucherType voucherType) {
		return searchVoucherDao.searchProductsByVoucher(voucherid, voucherType);
	}

	public List<ProductVoucherBase> getPvbByPro(long productid,
			VoucherType voucherType) {
		return searchVoucherDao.getPvbByPro(productid, voucherType);
	}

	public List<JsonVoSearchPurchaseByProduct> getSearchByProduct(
			SearchVoucherCondition searchVoucherCondition, Integer page,
			Integer rows) {
		return searchVoucherDao.getSearchPurchaseByProduct(
				searchVoucherCondition, page, rows);
	}

	public List<JsonVoSearchPurchaseByProduct> getSearchPurchaseByOrg(
			SearchVoucherCondition searchVoucherCondition, Integer page,
			Integer rows) {
		return searchVoucherDao.getSearchPurchaseByOrg(searchVoucherCondition,
				page, rows);
	}

	public Integer getSearchPurchaseByOrgCount(
			SearchVoucherCondition searchVoucherCondition) {
		return searchVoucherDao
				.getSearchPurchaseByOrgCount(searchVoucherCondition);
	}

	public List<JsonVoSearchVoucherByProduct> searchVoucherByOrg(
			SearchVoucherCondition searchVoucherCondition, Integer page,
			Integer rows) {
		return searchVoucherDao.searchVoucherByOrg(searchVoucherCondition,
				page, rows);
	}

	public Integer searchVoucherByOrgCount(
			SearchVoucherCondition searchVoucherCondition) {
		return searchVoucherDao.searchVoucherByOrgCount(searchVoucherCondition);
	}

	public Integer searchVoucherByOrgCount(
			SearchVoucherCondition searchVoucherCondition, Integer page,
			Integer rows) {
		return searchVoucherDao.searchVoucherByOrgCount(searchVoucherCondition);
	}

	public List<JsonVoSearchPurchaseByProduct> getSearchPurchaseByWH(
			SearchVoucherCondition searchVoucherCondition, Integer page,
			Integer rows) {
		return searchVoucherDao.getSearchPurchaseByWH(searchVoucherCondition,
				page, rows);
	}

	public Integer getSearchPurchaseByWHCount(
			SearchVoucherCondition searchVoucherCondition) {
		return searchVoucherDao.getSearchPurchaseByWHCount(searchVoucherCondition);
	}
	
	public Integer getStockByProductId(Long proId,Long wareHouseId,String stime,String etime) {
		SearchStockCondition ssc = new SearchStockCondition();
		ssc.setProductId(proId);
		ssc.setWareHouseId(wareHouseId);
		ssc.setStime(stime);
		ssc.setEtime(etime);
		return searchVoucherDao.getStockByProductId(ssc);
	}

	public SearchVoucherDao getSearchVoucherDao() {
		return searchVoucherDao;
	}

	@Autowired
	public void setSearchVoucherDao(SearchVoucherDao searchVoucherDao) {
		this.searchVoucherDao = searchVoucherDao;
	}

	@Override
	public List<JsonVoSearchVoucherByProduct> searchVoucherByProduct(
			SearchVoucherCondition searchVoucherCondition, Integer page,
			Integer rows) {
		return searchVoucherDao.searchVoucherByProduct(searchVoucherCondition,
				page, rows);
	}

	public Integer searchVoucherByProductCount(
			SearchVoucherCondition searchVoucherCondition) {
		return searchVoucherDao
				.searchVoucherByProductCount(searchVoucherCondition);
	}

	public Integer getSearchByProductCount(
			SearchVoucherCondition searchVoucherCondition) {
		return searchVoucherDao.getSearchByProductCount(searchVoucherCondition);
	}

	@Override
	public List<JsonVoSearchVoucherByProduct> searchVoucherByWH(
			SearchVoucherCondition searchVoucherCondition, Integer page,
			Integer rows) {
		// TODO Auto-generated method stub
		return searchVoucherDao.searchVoucherByWH(searchVoucherCondition, page,
				rows);
	}

	public Integer searchVoucherByWHCount(
			SearchVoucherCondition searchVoucherCondition) {
		return searchVoucherDao.searchVoucherByWHCount(searchVoucherCondition);
	}

	@Override
	public List<JsonVoSearchProfit> searchProfit(
			SearchVoucherCondition searchVoucherCondition, Integer page,
			Integer rows) {
		return searchVoucherDao.searchProfit(searchVoucherCondition, page, rows);
	}
	
	public Integer searchProfitCount(SearchVoucherCondition searchVoucherCondition) {
		return searchVoucherDao.searchProfitCount(searchVoucherCondition);
	}

	private WritableCellFormat getCellStyle(WritableCellFormat wcf, int flag)
			throws WriteException {
		wcf.setBorder(Border.ALL, BorderLineStyle.THIN);
		// 设置单元格内容对齐方式
		if (flag == 0)
			wcf.setAlignment(jxl.format.Alignment.LEFT);
		else if (flag == 1)
			wcf.setAlignment(jxl.format.Alignment.RIGHT);
		else
			wcf.setAlignment(jxl.format.Alignment.CENTRE);
		// 设置单元格内容为垂直居中
		wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
		return wcf;
	}

	/**
	 * 按照商品导出商品信息
	 */
	public void exportExcel(SearchVoucherCondition searchVoucherCondition, Integer page, Integer rows, OutputStream os) {
		List<JsonVoSearchPurchaseByProduct> list = this.getSearchByProduct(searchVoucherCondition, page, rows);
		if (list != null && list.size() > 0) {
			String stime, etime;
			String time, title = searchVoucherCondition.getTitle();
			if (!Validator.isEmpty(searchVoucherCondition.getStime())
					&& !Validator.isEmpty(searchVoucherCondition.getEtime())) {
				stime = searchVoucherCondition.getStime();
				etime = searchVoucherCondition.getEtime();
				time = " (统计日期：" + stime + "至" + etime + ")";
			} else {
				time = "(全部)";
			}
			if (!Validator.isEmpty(searchVoucherCondition.getPcId()) && searchVoucherCondition.getPcId() != 0) {
				ProductClass pc = this.productClassDao.getProductClassById(searchVoucherCondition.getPcId());
				title = pc.getName() + "的" + title;
			}

			try {
				// 创建文件
				WritableWorkbook book = Workbook.createWorkbook(os);
				// 生成名为“第一页”的工作表，参数0表示这是第一页
				WritableSheet sheet = book.createSheet("第一页 ", 0);
				sheet.mergeCells(0, 0, 8, 0);
				WritableFont font = new WritableFont(WritableFont.TIMES, 12, WritableFont.BOLD);
				WritableCellFormat leftFormat = new WritableCellFormat(font);// 标题靠左
				leftFormat = getCellStyle(leftFormat, 0);
				WritableCellFormat rightFormat = new WritableCellFormat(font);// 标题靠右
				rightFormat = getCellStyle(rightFormat, 1);

				WritableCellFormat format = new WritableCellFormat();// 内容靠左
				format = getCellStyle(format, 0);

				NumberFormat numFmt = new NumberFormat("#.00");
				WritableCellFormat format2 = new WritableCellFormat(numFmt);// 内容靠右
				format2 = getCellStyle(format2, 1);

				WritableCellFormat centerFormat = new WritableCellFormat(font);// 标题居中
				centerFormat = getCellStyle(centerFormat, 3);
				WritableCellFormat format3 = new WritableCellFormat();// 日期居中
				format3 = getCellStyle(format3, 3);

				if (VoucherType.delivery.equals(searchVoucherCondition.getVoucherType())) {// 销售
					// 设置每列的宽度
					sheet.setColumnView(0, 30);
					sheet.setColumnView(1, 15);
					sheet.setColumnView(2, 15);
					sheet.setColumnView(3, 15);
					sheet.setColumnView(4, 20);
					sheet.setColumnView(5, 22);
					sheet.setColumnView(6, 22);
					sheet.setColumnView(7, 22);
					sheet.setColumnView(8, 22);

					// 在Label对象的构造子中指名单元格位置是第一列第一行(0,0) 以及单元格内容为test
					// 将定义好的单元格添加到工作表中
					sheet.addCell(new Label(0, 0, title + time, centerFormat));
					sheet.addCell(new Label(0, 1, "名称", leftFormat));
					sheet.addCell(new Label(1, 1, "商品分类", leftFormat));
					sheet.addCell(new Label(2, 1, "规格", leftFormat));
					sheet.addCell(new Label(3, 1, "销售总数量", rightFormat));
					sheet.addCell(new Label(4, 1, "销售均价（元）", rightFormat));
					sheet.addCell(new Label(5, 1, "销售总金额（元）", rightFormat));
					sheet.addCell(new Label(6, 1, "退货总数量", rightFormat));
					sheet.addCell(new Label(7, 1, "退货均价（元）", rightFormat));
					sheet.addCell(new Label(8, 1, "退货总金额（元）", rightFormat));

					for (int i = 0; i < list.size(); i++) {
						JsonVoSearchPurchaseByProduct pro = list.get(i);
						int j = 0;
						sheet.addCell(new Label(j, i + 2, pro.getName(), format));
						sheet.addCell(new Label(j + 1, i + 2, pro.getProClassName(),
								format));
						sheet.addCell(new Label(j + 2, i + 2, pro.getSpec(),
								format));
						sheet.addCell(new jxl.write.Number(j + 3, i + 2, pro
								.getAmount().doubleValue(), format2));
						sheet.addCell(new jxl.write.Number(j + 4, i + 2, Double
								.valueOf(pro.getAveragePrice().toString()).doubleValue(),
								format2));
						sheet.addCell(new jxl.write.Number(j + 5, i + 2, Double
								.valueOf(pro.getTotalSubTotal().toString()).doubleValue(),
								format2));
						sheet.addCell(new jxl.write.Number(j + 6, i + 2, Double
								.valueOf(pro.getReturnAmount().toString()).doubleValue(),
								format2));
						sheet.addCell(new jxl.write.Number(j + 7, i + 2, Double
								.valueOf(pro.getAverageReturnPrice().toString()).doubleValue(),
								format2));
						sheet.addCell(new jxl.write.Number(j + 8, i + 2, Double
								.valueOf(pro.getReturnPrice().toString()).doubleValue(),
								format2));
					}
//				} else if (VoucherType.returndelivery
//						.equals(searchVoucherCondition.getVoucherType())) {// 销售退货
//					sheet.setColumnView(0, 30);
//					sheet.setColumnView(1, 15);
//					sheet.setColumnView(2, 15);
//					sheet.setColumnView(3, 15);
//					sheet.setColumnView(4, 20);
//					sheet.setColumnView(5, 22);
//					sheet.addCell(new Label(0, 0, title + time, centerFormat));
//					sheet.addCell(new Label(0, 1, "名称", leftFormat));
//					sheet.addCell(new Label(1, 1, "规格", leftFormat));
//					sheet.addCell(new Label(2, 1, "单位", leftFormat));
//					sheet.addCell(new Label(3, 1, "销售退货总数量", rightFormat));
//					sheet.addCell(new Label(4, 1, "销售退货均价（元）", rightFormat));
//					sheet.addCell(new Label(5, 1, "销售退货总金额（元）", rightFormat));
//
//					for (int i = 0; i < list.size(); i++) {
//						JsonVoSearchPurchaseByProduct pro = list.get(i);
//						int j = 0;
//						sheet.addCell(new Label(j, i + 2, pro.getName(), format));
//						sheet.addCell(new Label(j + 1, i + 2, pro.getSpec(),
//								format));
//						sheet.addCell(new Label(j + 2, i + 2, pro.getUnit(),
//								format));
//						sheet.addCell(new jxl.write.Number(j + 3, i + 2, pro
//								.getAmount().doubleValue(), format2));
//						sheet.addCell(new jxl.write.Number(j + 4, i + 2, Double
//								.valueOf(pro.getAveragePrice().toString()).doubleValue(),
//								format2));
//						sheet.addCell(new jxl.write.Number(j + 5, i + 2, Double
//								.valueOf(pro.getTotalSubTotal().toString()).doubleValue(),
//								format2));
//					}
				} else if (VoucherType.warehouse.equals(searchVoucherCondition
						.getVoucherType())) {// 进货
					sheet.setColumnView(0, 30);
					sheet.setColumnView(1, 15);
					sheet.setColumnView(2, 15);
					sheet.setColumnView(3, 15);
					sheet.setColumnView(4, 20);
					sheet.setColumnView(5, 22);
					sheet.setColumnView(6, 22);
					sheet.setColumnView(7, 22);
					sheet.setColumnView(8, 22);

					sheet.addCell(new Label(0, 0, title + time, centerFormat));
					// sheet.addCell(new Label(0, 1, time, format3));
					sheet.addCell(new Label(0, 1, "名称", leftFormat));
					sheet.addCell(new Label(2, 1, "商品分类", leftFormat));
					sheet.addCell(new Label(1, 1, "规格", leftFormat));
					sheet.addCell(new Label(3, 1, "进货总数量", rightFormat));
					sheet.addCell(new Label(4, 1, "进货均价（元）", rightFormat));
					sheet.addCell(new Label(5, 1, "进货总金额（元）", rightFormat));
					sheet.addCell(new Label(6, 1, "退货总数量", rightFormat));
					sheet.addCell(new Label(7, 1, "退货均价（元）", rightFormat));
					sheet.addCell(new Label(8, 1, "退货总金额（元）", rightFormat));

					for (int i = 0; i < list.size(); i++) {
						JsonVoSearchPurchaseByProduct pro = list.get(i);
						int j = 0;
						sheet.addCell(new Label(j, i + 2, pro.getName(), format));
						sheet.addCell(new Label(j + 2, i + 2, pro.getProClassName(),
								format));
						sheet.addCell(new Label(j + 1, i + 2, pro.getSpec(),
								format));
						sheet.addCell(new jxl.write.Number(j + 3, i + 2, pro
								.getAmount().doubleValue(), format2));
						sheet.addCell(new jxl.write.Number(j + 4, i + 2, Double
								.valueOf(pro.getAveragePrice().toString()).doubleValue(),
								format2));
						sheet.addCell(new jxl.write.Number(j + 5, i + 2, Double
								.valueOf(pro.getTotalSubTotal().toString()).doubleValue(),
								format2));
						sheet.addCell(new jxl.write.Number(j + 6, i + 2, Double
								.valueOf(pro.getReturnAmount().toString()).doubleValue(),
								format2));
						sheet.addCell(new jxl.write.Number(j + 7, i + 2, Double
								.valueOf(pro.getAverageReturnPrice().toString()).doubleValue(),
								format2));
						sheet.addCell(new jxl.write.Number(j + 8, i + 2, Double
								.valueOf(pro.getReturnPrice().toString()).doubleValue(),
								format2));
					}
//				} else if (VoucherType.returnwarehouse
//						.equals(searchVoucherCondition.getVoucherType())) {// 采购退货
//					sheet.setColumnView(0, 30);
//					sheet.setColumnView(1, 15);
//					sheet.setColumnView(2, 15);
//					sheet.setColumnView(3, 15);
//					sheet.setColumnView(4, 20);
//					sheet.setColumnView(5, 22);
//
//					sheet.addCell(new Label(0, 0, title + time, centerFormat));
//					sheet.addCell(new Label(0, 1, time, format3));
//					sheet.addCell(new Label(0, 1, "名称", leftFormat));
//					sheet.addCell(new Label(1, 1, "规格", leftFormat));
//					sheet.addCell(new Label(2, 1, "单位", leftFormat));
//					sheet.addCell(new Label(3, 1, "退货总数量", rightFormat));
//					sheet.addCell(new Label(4, 1, "退货均价（元）", rightFormat));
//					sheet.addCell(new Label(5, 1, "退货总金额（元）", rightFormat));
//
//					for (int i = 0; i < list.size(); i++) {
//						JsonVoSearchPurchaseByProduct pro = list.get(i);
//						int j = 0;
//						sheet.addCell(new Label(j, i + 2, pro.getName(), format));
//						sheet.addCell(new Label(j + 1, i + 2, pro.getSpec(),
//								format));
//						sheet.addCell(new Label(j + 2, i + 2, pro.getUnit(),
//								format));
//						sheet.addCell(new jxl.write.Number(j + 3, i + 2, pro
//								.getAmount().doubleValue(), format2));
//						sheet.addCell(new jxl.write.Number(j + 4, i + 2, Double
//								.valueOf(pro.getAveragePrice().toString()).doubleValue(),
//								format2));
//						sheet.addCell(new jxl.write.Number(j + 5, i + 2, Double
//								.valueOf(pro.getTotalSubTotal().toString()).doubleValue(),
//								format2));
//					}
				}
				book.write();
				book.close();
			} catch (Exception e) {
				System.out.println(e);
			}
		} else {
			try {
				WritableWorkbook book = Workbook.createWorkbook(os);
				WritableSheet sheet = book.createSheet("第一页 ", 0);
				WritableFont font = new WritableFont(WritableFont.TIMES, 12, WritableFont.BOLD);
				WritableCellFormat format = new WritableCellFormat(font);
				sheet.addCell(new Label(0, 1, "没有数据", format));
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 按照供应商导出商品信息
	 */
	public void exportExcelOrgs(SearchVoucherCondition searchVoucherCondition,
			Integer page, Integer rows, OutputStream os) {
		List<JsonVoSearchPurchaseByProduct> list = this.getSearchPurchaseByOrg(
				searchVoucherCondition, page, rows);
		if (list != null && list.size() > 0) {
			String stime, etime;
			String time, title = searchVoucherCondition.getTitle();
			if (!Validator.isEmpty(searchVoucherCondition.getStime())
					&& !Validator.isEmpty(searchVoucherCondition.getEtime())) {
				stime = searchVoucherCondition.getStime();
				etime = searchVoucherCondition.getEtime();
				time = " (统计日期：" + stime + "至" + etime + ")";
			} else {
				time = "(全部)";
			}
			if (!Validator.isEmpty(searchVoucherCondition.getOcId())
					&& searchVoucherCondition.getOcId() != 0) {
				OrganizationClass oc = this.orgClassDao
						.getOrganizationClassById(searchVoucherCondition
								.getOcId());
				title = oc.getName() + "的" + title;
			}
			try {
				// 创建文件
				WritableWorkbook book = Workbook.createWorkbook(os);
				// 生成名为“第一页”的工作表，参数0表示这是第一页
				WritableSheet sheet = book.createSheet("第一页 ", 0);
				sheet.mergeCells(0, 0, 7, 0);
				WritableFont font = new WritableFont(WritableFont.TIMES, 12,
						WritableFont.BOLD);
				WritableCellFormat leftFormat = new WritableCellFormat(font);// 标题靠左
				leftFormat = getCellStyle(leftFormat, 0);
				WritableCellFormat rightFormat = new WritableCellFormat(font);// 标题靠右
				rightFormat = getCellStyle(rightFormat, 1);

				WritableCellFormat format = new WritableCellFormat();// 内容靠左
				format = getCellStyle(format, 0);

				NumberFormat numFmt = new NumberFormat("#.00");
				WritableCellFormat format2 = new WritableCellFormat(numFmt);// 内容靠右
				format2 = getCellStyle(format2, 1);

				WritableCellFormat centerFormat = new WritableCellFormat(font);// 标题居中
				centerFormat = getCellStyle(centerFormat, 3);
				WritableCellFormat format3 = new WritableCellFormat();// 日期居中
				format3 = getCellStyle(format3, 3);

				if (VoucherType.delivery.equals(searchVoucherCondition
						.getVoucherType())) {// 销售
					// 设置每列的宽度
					sheet.setColumnView(0, 30);
					sheet.setColumnView(1, 15);
					sheet.setColumnView(2, 22);
					sheet.setColumnView(3, 22);
					sheet.setColumnView(4, 22);
					sheet.setColumnView(5, 22);
					sheet.setColumnView(6, 22);
					sheet.setColumnView(7, 22);

					sheet.addCell(new Label(0, 0, title + time, centerFormat));
					sheet.addCell(new Label(0, 1, "往来单位名称", leftFormat));
					sheet.addCell(new Label(1, 1, "往来单位类别", leftFormat));
					sheet.addCell(new Label(2, 1, "进货总数量", rightFormat));
					sheet.addCell(new Label(3, 1, "进货均价（元）", rightFormat));
					sheet.addCell(new Label(4, 1, "进货总金额（元）", rightFormat));
					sheet.addCell(new Label(5, 1, "退货总数量", rightFormat));
					sheet.addCell(new Label(6, 1, "退货均价（元）", rightFormat));
					sheet.addCell(new Label(7, 1, "退货总金额（元）", rightFormat));

					for (int i = 0; i < list.size(); i++) {
						JsonVoSearchPurchaseByProduct pro = list.get(i);
						int j = 0;
						sheet.addCell(new Label(j, i + 2, pro.getName(), format));
						sheet.addCell(new Label(j + 1, i + 2, pro.getOrgClassName(), format));
						sheet.addCell(new jxl.write.Number(j + 2, i + 2, pro
								.getAmount().doubleValue(), format2));
						sheet.addCell(new jxl.write.Number(j + 3, i + 2, Double
								.parseDouble(pro.getAveragePrice().toString()), format2));
						sheet.addCell(new jxl.write.Number(j + 4, i + 2, Double
								.parseDouble(pro.getTotalSubTotal().toString()), format2));
						sheet.addCell(new jxl.write.Number(j + 5, i + 2, Double
								.parseDouble(pro.getReturnAmount().toString()), format2));
						sheet.addCell(new jxl.write.Number(j + 6, i + 2, Double
								.parseDouble(pro.getAverageReturnPrice().toString()), format2));
						sheet.addCell(new jxl.write.Number(j + 7, i + 2, Double
								.parseDouble(pro.getReturnPrice().toString()), format2));
					}
				} else if (VoucherType.warehouse.equals(searchVoucherCondition
						.getVoucherType())) {// 采购
					sheet.setColumnView(0, 30);
					sheet.setColumnView(1, 15);
					sheet.setColumnView(2, 22);
					sheet.setColumnView(3, 22);
					sheet.setColumnView(4, 22);
					sheet.setColumnView(5, 22);
					sheet.setColumnView(6, 22);
					sheet.setColumnView(7, 22);

					sheet.addCell(new Label(0, 0, title + time, centerFormat));
					sheet.addCell(new Label(0, 1, "往来单位名称", leftFormat));
					sheet.addCell(new Label(1, 1, "往来单位类别", leftFormat));
					sheet.addCell(new Label(2, 1, "进货数量", rightFormat));
					sheet.addCell(new Label(3, 1, "进货均价（元）", rightFormat));
					sheet.addCell(new Label(4, 1, "进货总金额（元）", rightFormat));
					sheet.addCell(new Label(5, 1, "退货总数量", rightFormat));
					sheet.addCell(new Label(6, 1, "退货均价（元）", rightFormat));
					sheet.addCell(new Label(7, 1, "退货总金额（元）", rightFormat));

					for (int i = 0; i < list.size(); i++) {
						JsonVoSearchPurchaseByProduct pro = list.get(i);
						int j = 0;
						sheet.addCell(new Label(j, i + 2, pro.getName(), format));
						sheet.addCell(new Label(j + 1, i + 2, pro.getOrgClassName(), format));
						sheet.addCell(new jxl.write.Number(j + 2, i + 2, pro
								.getAmount().doubleValue(), format2));
						sheet.addCell(new jxl.write.Number(j + 3, i + 2, Double
								.parseDouble(pro.getAveragePrice().toString()), format2));
						sheet.addCell(new jxl.write.Number(j + 4, i + 2, Double
								.parseDouble(pro.getTotalSubTotal().toString()), format2));
						sheet.addCell(new jxl.write.Number(j + 5, i + 2, Double
								.parseDouble(pro.getReturnAmount().toString()), format2));
						sheet.addCell(new jxl.write.Number(j + 6, i + 2, Double
								.parseDouble(pro.getAverageReturnPrice().toString()), format2));
						sheet.addCell(new jxl.write.Number(j + 7, i + 2, Double
								.parseDouble(pro.getReturnPrice().toString()), format2));
					}
				}
				book.write();
				book.close();
			} catch (Exception e) {
				System.out.println(e);
			}
		} else {
			try {
				WritableWorkbook book = Workbook.createWorkbook(os);
				WritableSheet sheet = book.createSheet("第一页 ", 0);
				WritableFont font = new WritableFont(WritableFont.TIMES, 12, WritableFont.BOLD);
				WritableCellFormat format = new WritableCellFormat(font);
				sheet.addCell(new Label(0, 1, "没有数据", format));
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 按照仓库导出商品信息
	 */
	public void exportExcelWHs(SearchVoucherCondition searchVoucherCondition,
			Integer page, Integer rows, OutputStream os) {
		List<JsonVoSearchPurchaseByProduct> list = this.getSearchPurchaseByWH(
				searchVoucherCondition, page, rows);
		if (list != null && list.size() > 0) {
			String stime, etime;
			String time, title = searchVoucherCondition.getTitle();
			if (!Validator.isEmpty(searchVoucherCondition.getStime())
					&& !Validator.isEmpty(searchVoucherCondition.getEtime())) {
				stime = searchVoucherCondition.getStime();
				etime = searchVoucherCondition.getEtime();
				time = " (统计日期：" + stime + "至" + etime + ")";
			} else {
				time = "(全部)";
			}
			if (!Validator.isEmpty(searchVoucherCondition.getWcId())
					&& searchVoucherCondition.getWcId() != 0) {
				WareHouseClass wc = this.wareHouseClassDao
						.getWareHouseClassById(searchVoucherCondition.getWcId());
				title = wc.getName() + "的" + title;
			}
			try {
				// 创建文件
				WritableWorkbook book = Workbook.createWorkbook(os);
				// 生成名为“第一页”的工作表，参数0表示这是第一页
				WritableSheet sheet = book.createSheet("第一页 ", 0);
				sheet.mergeCells(0, 0, 7, 0);
				WritableFont font = new WritableFont(WritableFont.TIMES, 12,
						WritableFont.BOLD);
				WritableCellFormat leftFormat = new WritableCellFormat(font);// 标题靠左
				leftFormat = getCellStyle(leftFormat, 0);
				WritableCellFormat rightFormat = new WritableCellFormat(font);// 标题靠右
				rightFormat = getCellStyle(rightFormat, 1);

				WritableCellFormat format = new WritableCellFormat();// 内容靠左
				format = getCellStyle(format, 0);

				NumberFormat numFmt = new NumberFormat("#.00");
				WritableCellFormat format2 = new WritableCellFormat(numFmt);// 内容靠右
				format2 = getCellStyle(format2, 1);

				WritableCellFormat centerFormat = new WritableCellFormat(font);// 标题居中
				centerFormat = getCellStyle(centerFormat, 3);
				WritableCellFormat format3 = new WritableCellFormat();// 日期居中
				format3 = getCellStyle(format3, 3);

				if (VoucherType.delivery.equals(searchVoucherCondition
						.getVoucherType())) {// 销售
					sheet.setColumnView(0, 30);
					sheet.setColumnView(1, 22);
					sheet.setColumnView(2, 22);
					sheet.setColumnView(3, 22);
					sheet.setColumnView(4, 22);
					sheet.setColumnView(5, 22);
					sheet.setColumnView(6, 22);
					sheet.setColumnView(7, 22);

					sheet.addCell(new Label(0, 0, title + time, centerFormat));
					sheet.addCell(new Label(0, 1, "仓库名称", leftFormat));
					sheet.addCell(new Label(1, 1, "仓库类别", leftFormat));
					sheet.addCell(new Label(2, 1, "进货总数量", rightFormat));
					sheet.addCell(new Label(3, 1, "进货均价（元）", rightFormat));
					sheet.addCell(new Label(4, 1, "进货总金额（元）", rightFormat));
					sheet.addCell(new Label(5, 1, "退货总数量", rightFormat));
					sheet.addCell(new Label(6, 1, "退货均价（元）", rightFormat));
					sheet.addCell(new Label(7, 1, "退货总金额（元）", rightFormat));

					for (int i = 0; i < list.size(); i++) {
						JsonVoSearchPurchaseByProduct pro = list.get(i);
						int j = 0;
						sheet.addCell(new Label(j, i + 2, pro.getName(), format));
						sheet.addCell(new Label(j + 1, i + 2, pro.getWhClassName(), format));
						sheet.addCell(new jxl.write.Number(j + 2, i + 2, pro
								.getAmount().doubleValue(), format2));
						sheet.addCell(new jxl.write.Number(j + 3, i + 2, Double
								.parseDouble(pro.getAveragePrice().toString()), format2));
						sheet.addCell(new jxl.write.Number(j + 4, i + 2, Double
								.parseDouble(pro.getTotalSubTotal().toString()), format2));
						sheet.addCell(new jxl.write.Number(j + 5, i + 2, Double
								.parseDouble(pro.getReturnAmount().toString()), format2));
						sheet.addCell(new jxl.write.Number(j + 6, i + 2, Double
								.parseDouble(pro.getAverageReturnPrice().toString()), format2));
						sheet.addCell(new jxl.write.Number(j + 7, i + 2, Double
								.parseDouble(pro.getReturnPrice().toString()), format2));
					}
				} else if (VoucherType.warehouse.equals(searchVoucherCondition
						.getVoucherType())) {// 采购
					sheet.setColumnView(0, 30);
					sheet.setColumnView(1, 22);
					sheet.setColumnView(2, 22);
					sheet.setColumnView(3, 22);
					sheet.setColumnView(4, 22);
					sheet.setColumnView(5, 22);
					sheet.setColumnView(6, 22);
					sheet.setColumnView(7, 22);

					sheet.addCell(new Label(0, 0, title + time, centerFormat));
					sheet.addCell(new Label(0, 1, "仓库名称", leftFormat));
					sheet.addCell(new Label(1, 1, "进货总数量", rightFormat));
					sheet.addCell(new Label(2, 1, "进货均价（元）", rightFormat));
					sheet.addCell(new Label(3, 1, "进货总数量（元）", rightFormat));
					sheet.addCell(new Label(5, 1, "退货总数量", rightFormat));
					sheet.addCell(new Label(6, 1, "退货均价（元）", rightFormat));
					sheet.addCell(new Label(7, 1, "退货总金额（元）", rightFormat));

					for (int i = 0; i < list.size(); i++) {
						JsonVoSearchPurchaseByProduct pro = list.get(i);
						int j = 0;
						sheet.addCell(new Label(j, i + 2, pro.getName(), format));
						sheet.addCell(new Label(j + 1, i + 2, pro.getWhClassName(), format));
						sheet.addCell(new jxl.write.Number(j + 2, i + 2, pro
								.getAmount().doubleValue(), format2));
						sheet.addCell(new jxl.write.Number(j + 3, i + 2, Double
								.parseDouble(pro.getAveragePrice().toString()), format2));
						sheet.addCell(new jxl.write.Number(j + 4, i + 2, Double
								.parseDouble(pro.getTotalSubTotal().toString()), format2));
						sheet.addCell(new jxl.write.Number(j + 5, i + 2, Double
								.parseDouble(pro.getReturnAmount().toString()), format2));
						sheet.addCell(new jxl.write.Number(j + 6, i + 2, Double
								.parseDouble(pro.getAverageReturnPrice().toString()), format2));
						sheet.addCell(new jxl.write.Number(j + 7, i + 2, Double
								.parseDouble(pro.getReturnPrice().toString()), format2));
					}
				}
				book.write();
				book.close();
			} catch (Exception e) {
				System.out.println(e);
			}
		} else {
			try {
				WritableWorkbook book = Workbook.createWorkbook(os);
				WritableSheet sheet = book.createSheet("第一页 ", 0);
				WritableFont font = new WritableFont(WritableFont.TIMES, 12, WritableFont.BOLD);
				WritableCellFormat format = new WritableCellFormat(font);
				sheet.addCell(new Label(0, 1, "没有数据", format));
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	// 导出进货统计- 按商品 - 进货明细查询
	public void exportWareHouseDetail(
			SearchVoucherCondition searchVoucherCondition, Integer page,
			Integer rows, OutputStream os) {
		List<JsonVoSearchVoucherByProduct> list = null;
		if (!Validator.isEmpty(searchVoucherCondition.getProId())
				&& searchVoucherCondition.getProId() != 0) {
			list = this.searchVoucherByProduct(searchVoucherCondition, page,
					rows);
		} else if (!Validator.isEmpty(searchVoucherCondition.getOrgId())
				&& searchVoucherCondition.getOrgId() != 0) {
			list = this.searchVoucherByOrg(searchVoucherCondition, page, rows);
		} else if (!Validator.isEmpty(searchVoucherCondition.getWhId())
				&& searchVoucherCondition.getWhId() != 0) {
			list = this.searchVoucherByWH(searchVoucherCondition, page, rows);
		}
		if (list != null && list.size() > 0) {
			try {
				WritableWorkbook book = Workbook.createWorkbook(os);
				WritableSheet sheet = book.createSheet("第一页 ", 0);
				sheet.mergeCells(0, 0, 6, 0);
				WritableFont font = new WritableFont(WritableFont.TIMES, 12,
						WritableFont.BOLD);
				WritableCellFormat leftFormat = new WritableCellFormat(font);// 标题靠左
				leftFormat = getCellStyle(leftFormat, 0);
				WritableCellFormat rightFormat = new WritableCellFormat(font);// 标题靠右
				rightFormat = getCellStyle(rightFormat, 1);

				WritableCellFormat format = new WritableCellFormat();// 内容靠左
				format = getCellStyle(format, 0);

				NumberFormat numFmt = new NumberFormat("#.00");
				WritableCellFormat format2 = new WritableCellFormat(numFmt);// 内容靠右
				format2 = getCellStyle(format2, 1);

				WritableCellFormat centerFormat = new WritableCellFormat(font);// 标题居中
				centerFormat = getCellStyle(centerFormat, 3);

				sheet.setColumnView(0, 30);
				sheet.setColumnView(1, 30);
				sheet.setColumnView(2, 15);
				sheet.setColumnView(3, 20);
				sheet.setColumnView(4, 15);
				sheet.setColumnView(5, 15);
				
				if (!Validator.isEmpty(searchVoucherCondition.getProId())
						&& searchVoucherCondition.getProId() != 0) {
					sheet.setColumnView(6, 22);
					sheet.addCell(new Label(6, 1, "小计（元）", rightFormat));
				}
				

				sheet.addCell(new Label(0, 0,
						searchVoucherCondition.getTitle(), centerFormat));
				sheet.addCell(new Label(0, 1, "单据日期", leftFormat));
				sheet.addCell(new Label(1, 1, "单据编号", leftFormat));
				sheet.addCell(new Label(2, 1, "单据类型", leftFormat));
				sheet.addCell(new Label(3, 1, "往来单位", leftFormat));
				sheet.addCell(new Label(4, 1, "仓库", leftFormat));
				sheet.addCell(new Label(5, 1, "数量", rightFormat));
				

				for (int i = 0; i < list.size(); i++) {
					JsonVoSearchVoucherByProduct pro = list.get(i);
					VoucherType voucherType = Enum.valueOf(VoucherType.class,pro.getVoucherType());
					int j = 0;
					sheet.addCell(new Label(j, i + 2, pro.getDate(), format));
					sheet.addCell(new Label(j + 1, i + 2, pro.getNo(), format));
					sheet.addCell(new Label(j + 2, i + 2, voucherType.getCaption(),
							format));
					sheet.addCell(new Label(j + 3, i + 2, pro.getOrg(), format));
					sheet.addCell(new Label(j + 4, i + 2, pro.getWarehouse(),
							format));
					sheet.addCell(new jxl.write.Number(j + 5, i + 2, Double
							.valueOf(pro.getAmount().doubleValue()), format2));
					if (!Validator.isEmpty(searchVoucherCondition.getProId())
							&& searchVoucherCondition.getProId() != 0) {
						sheet.addCell(new jxl.write.Number(j + 6, i + 2, Double
								.parseDouble(pro.getSubtotal().toString()), format2));
					}
				}
				book.write();
				book.close();
			} catch (Exception e) {
				System.out.println(e);
			}
		} else {
			try {
				WritableWorkbook book = Workbook.createWorkbook(os);
				WritableSheet sheet = book.createSheet("第一页 ", 0);
				WritableFont font = new WritableFont(WritableFont.TIMES, 12, WritableFont.BOLD);
				WritableCellFormat format = new WritableCellFormat(font);
				sheet.addCell(new Label(0, 1, "没有数据", format));
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	// 导出进货退货统计-详细
	public void exportReturnWareHouseDetail(
			SearchVoucherCondition searchVoucherCondition, Integer page,
			Integer rows, OutputStream os) {
		List<JsonVoSearchVoucherByProduct> list = null;
		if (!Validator.isEmpty(searchVoucherCondition.getProId())
				&& searchVoucherCondition.getProId() != 0) {
			list = this.searchVoucherByProduct(searchVoucherCondition, page,
					rows);
		} else if (!Validator.isEmpty(searchVoucherCondition.getOrgId())
				&& searchVoucherCondition.getOrgId() != 0) {
			list = this.searchVoucherByOrg(searchVoucherCondition, page, rows);
		} else if (!Validator.isEmpty(searchVoucherCondition.getWhId())
				&& searchVoucherCondition.getWhId() != 0) {
			list = this.searchVoucherByWH(searchVoucherCondition, page, rows);
		}
		if (list != null && list.size() > 0) {
			try {
				WritableWorkbook book = Workbook.createWorkbook(os);
				WritableSheet sheet = book.createSheet("第一页 ", 0);
				sheet.mergeCells(0, 0, 6, 0);
				WritableFont font = new WritableFont(WritableFont.TIMES, 12,
						WritableFont.BOLD);
				WritableCellFormat leftFormat = new WritableCellFormat(font);// 标题靠左
				leftFormat = getCellStyle(leftFormat, 0);
				WritableCellFormat rightFormat = new WritableCellFormat(font);// 标题靠右
				rightFormat = getCellStyle(rightFormat, 1);

				WritableCellFormat format = new WritableCellFormat();// 内容靠左
				format = getCellStyle(format, 0);

				NumberFormat numFmt = new NumberFormat("#.00");
				WritableCellFormat format2 = new WritableCellFormat(numFmt);// 内容靠右
				format2 = getCellStyle(format2, 1);

				WritableCellFormat centerFormat = new WritableCellFormat(font);// 标题居中
				centerFormat = getCellStyle(centerFormat, 3);

				sheet.setColumnView(0, 30);
				sheet.setColumnView(1, 30);
				sheet.setColumnView(2, 15);
				sheet.setColumnView(3, 20);
				sheet.setColumnView(4, 15);
				sheet.setColumnView(5, 15);
				sheet.setColumnView(6, 22);

				sheet.addCell(new Label(0, 0,
						searchVoucherCondition.getTitle(), centerFormat));
				sheet.addCell(new Label(0, 1, "单据日期", leftFormat));
				sheet.addCell(new Label(1, 1, "单据编号", leftFormat));
				sheet.addCell(new Label(2, 1, "单据类型", leftFormat));
				sheet.addCell(new Label(3, 1, "往来单位", leftFormat));
				sheet.addCell(new Label(4, 1, "退货仓库", leftFormat));
				sheet.addCell(new Label(5, 1, "退货数量", rightFormat));
				sheet.addCell(new Label(6, 1, "单据金额（元）", rightFormat));

				for (int i = 0; i < list.size(); i++) {
					JsonVoSearchVoucherByProduct pro = list.get(i);
					int j = 0;
					sheet.addCell(new Label(j, i + 2, pro.getDate(), format));
					sheet.addCell(new Label(j + 1, i + 2, pro.getNo(), format));
					sheet.addCell(new Label(j + 2, i + 2, pro.getVoucherType(),
							format));
					sheet.addCell(new Label(j + 3, i + 2, pro.getOrg(), format));
					sheet.addCell(new Label(j + 4, i + 2, pro.getWarehouse(),
							format));
					sheet.addCell(new jxl.write.Number(j + 5, i + 2, Double
							.valueOf(pro.getAmount().doubleValue()), format2));
					sheet.addCell(new jxl.write.Number(j + 6, i + 2, Double
							.parseDouble(pro.getSubtotal().toString()), format2));
				}
				book.write();
				book.close();
			} catch (Exception e) {
				System.out.println(e);
			}
		} else {
			try {
				WritableWorkbook book = Workbook.createWorkbook(os);
				WritableSheet sheet = book.createSheet("第一页 ", 0);
				WritableFont font = new WritableFont(WritableFont.TIMES, 12, WritableFont.BOLD);
				WritableCellFormat format = new WritableCellFormat(font);
				sheet.addCell(new Label(0, 1, "没有数据", format));
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	// 导出销售统计-- 按商品 - 销售明细查询
	public void exportDeliveryDetail(
			SearchVoucherCondition searchVoucherCondition, Integer page,
			Integer rows, OutputStream os) {
		List<JsonVoSearchVoucherByProduct> list = null;
		if (!Validator.isEmpty(searchVoucherCondition.getProId())
				&& searchVoucherCondition.getProId() != 0) {
			list = this.searchVoucherByProduct(searchVoucherCondition, page,
					rows);
		} else if (!Validator.isEmpty(searchVoucherCondition.getOrgId())
				&& searchVoucherCondition.getOrgId() != 0) {
			list = this.searchVoucherByOrg(searchVoucherCondition, page, rows);
		} else if (!Validator.isEmpty(searchVoucherCondition.getWhId())
				&& searchVoucherCondition.getWhId() != 0) {
			list = this.searchVoucherByWH(searchVoucherCondition, page, rows);
		}
		if (list != null && list.size() > 0) {
			try {
				WritableWorkbook book = Workbook.createWorkbook(os);
				WritableSheet sheet = book.createSheet("第一页 ", 0);
				sheet.mergeCells(0, 0, 6, 0);
				WritableFont font = new WritableFont(WritableFont.TIMES, 12,
						WritableFont.BOLD);
				WritableCellFormat leftFormat = new WritableCellFormat(font);// 标题靠左
				leftFormat = getCellStyle(leftFormat, 0);
				WritableCellFormat rightFormat = new WritableCellFormat(font);// 标题靠右
				rightFormat = getCellStyle(rightFormat, 1);

				WritableCellFormat format = new WritableCellFormat();// 内容靠左
				format = getCellStyle(format, 0);

				NumberFormat numFmt = new NumberFormat("#.00");
				WritableCellFormat format2 = new WritableCellFormat(numFmt);// 内容靠右
				format2 = getCellStyle(format2, 1);

				WritableCellFormat centerFormat = new WritableCellFormat(font);// 标题居中
				centerFormat = getCellStyle(centerFormat, 3);

				sheet.setColumnView(0, 30);
				sheet.setColumnView(1, 30);
				sheet.setColumnView(2, 15);
				sheet.setColumnView(3, 20);
				sheet.setColumnView(4, 15);
				sheet.setColumnView(5, 15);

				if (!Validator.isEmpty(searchVoucherCondition.getProId())
						&& searchVoucherCondition.getProId() != 0) {
					sheet.setColumnView(6, 22);
					sheet.addCell(new Label(6, 1, "小计（元）", rightFormat));
				}

				sheet.addCell(new Label(0, 0,
						searchVoucherCondition.getTitle(), centerFormat));
				sheet.addCell(new Label(0, 1, "单据日期", leftFormat));
				sheet.addCell(new Label(1, 1, "单据编号", leftFormat));
				sheet.addCell(new Label(2, 1, "单据类型", leftFormat));
				sheet.addCell(new Label(3, 1, "往来单位", leftFormat));
				sheet.addCell(new Label(4, 1, "仓库", leftFormat));
				sheet.addCell(new Label(5, 1, "数量", rightFormat));
				sheet.addCell(new Label(6, 1, "小计（元）", rightFormat));

				for (int i = 0; i < list.size(); i++) {
					JsonVoSearchVoucherByProduct pro = list.get(i);
					VoucherType voucherType = Enum.valueOf(VoucherType.class,pro.getVoucherType());
					int j = 0;
					sheet.addCell(new Label(j, i + 2, pro.getDate(), format));
					sheet.addCell(new Label(j + 1, i + 2, pro.getNo(), format));
					sheet.addCell(new Label(j + 2, i + 2, voucherType.getCaption(),
							format));
					sheet.addCell(new Label(j + 3, i + 2, pro.getOrg(), format));
					sheet.addCell(new Label(j + 4, i + 2, pro.getWarehouse(),
							format));
					sheet.addCell(new jxl.write.Number(j + 5, i + 2, Double
							.valueOf(pro.getAmount().doubleValue()), format2));
					
					if (!Validator.isEmpty(searchVoucherCondition.getProId())
							&& searchVoucherCondition.getProId() != 0) {
						sheet.addCell(new jxl.write.Number(j + 6, i + 2, Double
								.parseDouble(pro.getSubtotal().toString()), format2));
					}
				}
				book.write();
				book.close();
			} catch (Exception e) {
				System.out.println(e);
			}
		} else {
			try {
				WritableWorkbook book = Workbook.createWorkbook(os);
				WritableSheet sheet = book.createSheet("第一页 ", 0);
				WritableFont font = new WritableFont(WritableFont.TIMES, 12, WritableFont.BOLD);
				WritableCellFormat format = new WritableCellFormat(font);
				sheet.addCell(new Label(0, 1, "没有数据", format));
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	// 导出销售退货统计-详细
	public void exportReturnDeliveryDetail(
			SearchVoucherCondition searchVoucherCondition, Integer page,
			Integer rows, OutputStream os) {
		List<JsonVoSearchVoucherByProduct> list = null;
		if (!Validator.isEmpty(searchVoucherCondition.getProId())
				&& searchVoucherCondition.getProId() != 0) {
			list = this.searchVoucherByProduct(searchVoucherCondition, page,
					rows);
		} else if (!Validator.isEmpty(searchVoucherCondition.getOrgId())
				&& searchVoucherCondition.getOrgId() != 0) {
			list = this.searchVoucherByOrg(searchVoucherCondition, page, rows);
		} else if (!Validator.isEmpty(searchVoucherCondition.getWhId())
				&& searchVoucherCondition.getWhId() != 0) {
			list = this.searchVoucherByWH(searchVoucherCondition, page, rows);
		}
		if (list != null && list.size() > 0) {
			try {
				WritableWorkbook book = Workbook.createWorkbook(os);
				WritableSheet sheet = book.createSheet("第一页 ", 0);
				sheet.mergeCells(0, 0, 6, 0);
				WritableFont font = new WritableFont(WritableFont.TIMES, 12,
						WritableFont.BOLD);
				WritableCellFormat leftFormat = new WritableCellFormat(font);// 标题靠左
				leftFormat = getCellStyle(leftFormat, 0);
				WritableCellFormat rightFormat = new WritableCellFormat(font);// 标题靠右
				rightFormat = getCellStyle(rightFormat, 1);

				WritableCellFormat format = new WritableCellFormat();// 内容靠左
				format = getCellStyle(format, 0);

				NumberFormat numFmt = new NumberFormat("#.00");
				WritableCellFormat format2 = new WritableCellFormat(numFmt);// 内容靠右
				format2 = getCellStyle(format2, 1);

				WritableCellFormat centerFormat = new WritableCellFormat(font);// 标题居中
				centerFormat = getCellStyle(centerFormat, 3);

				sheet.setColumnView(0, 30);
				sheet.setColumnView(1, 30);
				sheet.setColumnView(2, 15);
				sheet.setColumnView(3, 20);
				sheet.setColumnView(4, 15);
				sheet.setColumnView(5, 15);
				sheet.setColumnView(6, 22);

				sheet.addCell(new Label(0, 0,
						searchVoucherCondition.getTitle(), centerFormat));
				sheet.addCell(new Label(0, 1, "单据日期", leftFormat));
				sheet.addCell(new Label(1, 1, "单据编号", leftFormat));
				sheet.addCell(new Label(2, 1, "单据类型", leftFormat));
				sheet.addCell(new Label(3, 1, "往来单位", leftFormat));
				sheet.addCell(new Label(4, 1, "退货仓库", leftFormat));
				sheet.addCell(new Label(5, 1, "退货数量", rightFormat));
				sheet.addCell(new Label(6, 1, "单据金额（元）", rightFormat));

				for (int i = 0; i < list.size(); i++) {
					JsonVoSearchVoucherByProduct pro = list.get(i);
					int j = 0;
					sheet.addCell(new Label(j, i + 2, pro.getDate(), format));
					sheet.addCell(new Label(j + 1, i + 2, pro.getNo(), format));
					sheet.addCell(new Label(j + 2, i + 2, pro.getVoucherType(),
							format));
					sheet.addCell(new Label(j + 3, i + 2, pro.getOrg(), format));
					sheet.addCell(new Label(j + 4, i + 2, pro.getWarehouse(),
							format));
					sheet.addCell(new jxl.write.Number(j + 5, i + 2, Double
							.valueOf(pro.getAmount().doubleValue()), format2));
					sheet.addCell(new jxl.write.Number(j + 6, i + 2, Double
							.parseDouble(pro.getSubtotal().toString()), format2));
				}
				book.write();
				book.close();
			} catch (Exception e) {
				System.out.println(e);
			}
		} else {
			try {
				WritableWorkbook book = Workbook.createWorkbook(os);
				WritableSheet sheet = book.createSheet("第一页 ", 0);
				WritableFont font = new WritableFont(WritableFont.TIMES, 12, WritableFont.BOLD);
				WritableCellFormat format = new WritableCellFormat(font);
				sheet.addCell(new Label(0, 1, "没有数据", format));
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
	
	public Integer getWareHouseNumByProId(SearchStockCondition searchStockConditon){
		return searchVoucherDao.getWareHouseNumByProId(searchStockConditon);
	}
	
	public Integer getReturnWareHouseNumByProId(SearchStockCondition searchStockConditon){
		return searchVoucherDao.getReturnWareHouseNumByProId(searchStockConditon);
	}
	
	public Integer getDeliveryNumByPorId(SearchStockCondition searchStockConditon){
		return searchVoucherDao.getDeliveryNumByPorId(searchStockConditon);
	}

	public Integer getReturnDeliveryNumByProId(SearchStockCondition searchStockConditon){
		return searchVoucherDao.getReturnDeliveryNumByProId(searchStockConditon);
	}

	/**
	 * 获取实际进货总数，去掉了退货数量，DWR调用，进货退货使用
	 */
	public Integer getWareHouseNumDWR(SearchStockCondition searchStockConditon){
		return getWareHouseNumByProId(searchStockConditon) - getReturnWareHouseNumByProId(searchStockConditon);
	}
	
	/**
	 * 获取实际的销售数量
	 */
	public Integer getDeliveryNumDWR(SearchStockCondition searchStockConditon){
		return getDeliveryNumByPorId(searchStockConditon) - getReturnDeliveryNumByProId(searchStockConditon);
	}
	
	public ProductClassDao getProductClassDao() {
		return productClassDao;
	}
	
	@Autowired
	public void setProductClassDao(ProductClassDao productClassDao) {
		this.productClassDao = productClassDao;
	}

	public OrganizationClassDao getOrgClassDao() {
		return orgClassDao;
	}

	@Autowired
	public void setOrgClassDao(OrganizationClassDao orgClassDao) {
		this.orgClassDao = orgClassDao;
	}

	public WareHouseClassDao getWareHouseClassDao() {
		return wareHouseClassDao;
	}

	@Autowired
	public void setWareHouseClassDao(WareHouseClassDao wareHouseClassDao) {
		this.wareHouseClassDao = wareHouseClassDao;
	}

}
